package com.zhiyou.dao;

import com.zhiyou.model.Contract;
import com.zhiyou.model.House;
import com.zhiyou.model.Lessee;
import com.zhiyou.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class ContractDao {
    public int getContractNum(HashMap<String,String> keywordMap) {
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;
        ResultSet rs=null;
        int total=0;
        String keyword = keywordMap.get("keyword");
        String startTime = keywordMap.get("startTime");
        String endTime = keywordMap.get("endTime");
        String sql="select count(cid) total from contract where cnum like concat(\"%\",?,\"%\")"
                +" and ctime between ? and ?";
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,keyword == null? "":keyword);
            ps.setString(2,startTime == null||startTime.equals("")? "2000-01-01":startTime);
            ps.setString(3,endTime == null||endTime.equals("")? "3000-01-01":endTime);
            System.out.println("日志 : 查个数sql"+ps.toString());
            rs=ps.executeQuery();
            while (rs.next()){
                total = rs.getInt("total");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return total;
    }

    public List<Contract> getContract(int pageNo,int pageSize,HashMap<String,String> keywordMap) {
        ArrayList<Contract> contracts = new ArrayList<>();
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;
        ResultSet rs=null;
        String keyword = keywordMap.get("keyword");
        String startTime = keywordMap.get("startTime");
        String endTime = keywordMap.get("endTime");
        String sql="select "
                +"cid,cnum,chid,clid,ctime,cstartTime,cendTime,ctotalMoney,cpayType,haddress,lname"
                +" from contract c"
                +" inner join house h on c.chid=h.hid "
                +" inner join lessee l on c.clid=l.lid"
                +" where cnum like concat(\"%\",?,\"%\")"
                +" and ctime between ? and ? limit ?,?";
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,keyword == null? "":keyword);
            ps.setString(2,startTime == null||startTime.equals("")? "2000-01-01":startTime);
            ps.setString(3,endTime == null||endTime.equals("")? "3000-01-01":endTime);
            ps.setInt(4,(pageNo-1)*pageSize);
            ps.setInt(5,pageSize);
            System.out.println("日志 : 查全部sql"+ps.toString());
            rs = ps.executeQuery();
            while(rs.next()){
                Contract contract=new Contract();
                contract.setCid(rs.getInt("cid"));
                contract.setCnum(rs.getString("cnum"));
                contract.setChid(rs.getInt("chid"));
                contract.setClid(rs.getInt("clid"));
                contract.setCtime(rs.getDate("ctime"));
                contract.setCstartTime(rs.getDate("cstartTime"));
                contract.setCendTime(rs.getDate("cendTime"));
                contract.setCtotalMoney(rs.getString("ctotalMoney"));
                contract.setCpayType(rs.getInt("cpayType"));
                House house = new House();
                house.setHaddress(rs.getString("haddress"));
                contract.setHouse(house);
                Lessee lessee = new Lessee();
                lessee.setLname(rs.getString("lname"));
                contract.setLessee(lessee);
                contracts.add(contract);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return contracts;
    }

    public void deleteById(int cid) {
        Connection conn = DBUtil.getConn();
        String sql="delete from contract where cid=?";
        PreparedStatement ps=null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,cid);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps);
        }
    }

    public void addNew(String cnum, int chid, int clid, String ctime, String cstartTime, String cendTime, String ctotalMoney, int cpayType) {
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;
        String sql="insert into contract(cnum,chid,clid,ctime,cstartTime,cendTime,ctotalMoney,cpayType) values(?,?,?,?,?,?,?,?) ";
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,cnum);
            ps.setInt(2,chid);
            ps.setInt(3,clid);
            ps.setString(4,ctime);
            ps.setString(5,cstartTime);
            ps.setString(6,cendTime);
            ps.setString(7,ctotalMoney);
            ps.setInt(8,cpayType);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps);
        }
    }

    public Contract selectById(int cid) {
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;
        ResultSet rs=null;
        Contract contract=null;
        String sql="select * from contract where cid=?";
        try {
            ps=conn.prepareStatement(sql);
            ps.setInt(1,cid);
            rs=ps.executeQuery();
            while (rs.next()){
                contract = new Contract();
                contract.setCid(rs.getInt("cid"));
                contract.setCnum(rs.getString("cnum"));
                contract.setChid(rs.getInt("chid"));
                contract.setClid(rs.getInt("clid"));
                contract.setCtime(rs.getDate("ctime"));
                contract.setCstartTime(rs.getDate("cstartTime"));
                contract.setCendTime(rs.getDate("cendTime"));
                contract.setCtotalMoney(rs.getString("ctotalMoney"));
                contract.setCpayType(rs.getInt("cpayType"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return contract;
    }

    public void updateNew(int cid, String cnum, int chid, int clid, String ctime, String cstartTime, String cendTime, String ctotalMoney, int cpayType) {
        Connection conn = DBUtil.getConn();
        PreparedStatement ps=null;
        String sql="update contract set cnum=?,chid=?,clid=?,ctime=?,cstartTime=?,cendTime=?,ctotalMoney=?,cpayType=? where cid=?";
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,cnum);
            ps.setInt(2,chid);
            ps.setInt(3,clid);
            ps.setString(4,ctime);
            ps.setString(5,cstartTime);
            ps.setString(6,cendTime);
            ps.setString(7,ctotalMoney);
            ps.setInt(8,cpayType);
            ps.setInt(9,cid);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeAll(conn,ps);
        }
    }
}
